﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
//using Microsoft.SqlServer.Management.Smo.Wmi;
using System.Xml;
using System.IO;
using System.Security.AccessControl;
using ClassExecute;


namespace SMS
{
    public partial class ConfigDatabaseForm : Form
    {
        
        public ConfigDatabaseForm()
        {
            InitializeComponent();
            Enabled = false;
            StatusForm statusform = new StatusForm();
            statusform.MdiParent = this.MdiParent;
            statusform.Show("Vui lòng chờ tải danh sách máy chủ");
            LoadServer();
            statusform.Close();
            Enabled = true;
            Cursor.Current = Cursors.Default;
        }

        private void LoadServer()
        {
            cbbMayChu.Items.Clear();
            // Create a DataTable where we enumerate the available servers
            DataTable dtServers = SmoApplication.EnumAvailableSqlServers(true);
            // If there are any servers at all
            if (dtServers.Rows.Count > 0)
            {
                // Loop through each server in the DataTable
                foreach (DataRow drServer in dtServers.Rows)
                {
                    // Add the name to the combobox
                    cbbMayChu.Items.Add(drServer["Name"]);
                }
            }            

        }
        
        private void btnRefresh_Click(object sender, EventArgs e)
        {
            //Cursor.Current = Cursors.WaitCursor;
            Enabled = false;
            StatusForm statusform = new StatusForm();
            statusform.MdiParent = this.MdiParent;            
            statusform.Show("Vui lòng chờ tải danh sách máy chủ");
            LoadServer();
            statusform.Close();
            Enabled = true;
            Cursor.Current = Cursors.Default;
        }
        // Load List Data base name SELECT [NAME] FROM [sysdatabases] ORDER BY [DbId]
        public void LoadDataBase(string strConnection)
        {
            string error="";
            string strSQLQuery = "SELECT [NAME] FROM [sysdatabases] ORDER BY [DbId]";
            cbbListDataBaseName.Items.Clear();
            ClassExecute.ClassExecute obj = new ClassExecute.ClassExecute();
            DataTable dtServers = obj.Get_Data_DataTable(strConnection, ref error, strSQLQuery, CommandType.Text);
            // If there are any servers at all
            if (dtServers.Rows.Count > 0)
            {
                // Loop through each server in the DataTable
                foreach (DataRow drServer in dtServers.Rows)
                {
                    // Add the name to the combobox
                    cbbListDataBaseName.Items.Add(drServer["Name"]);
                }
            }   
        }
             
        private void cbbListDataBaseName_DropDown(object sender, EventArgs e)
        {
            Enabled = false;
            StatusForm statusform = new StatusForm();
            statusform.MdiParent = this.MdiParent;
            statusform.Show("Vui lòng chờ tải danh sách cơ sở dữ liệu");
            LoadDataBase(getConnectionString());
            statusform.Close();
            Enabled = true;
            Cursor.Current = Cursors.Default;
        }
        private void radiobtnTaiKhoanSqlServer_CheckedChanged(object sender, EventArgs e)
        {
            if (radiobtnTaiKhoanSqlServer.Checked)
            {
                txtTaiKhoan.Enabled = true;
                txtMatKhau.Enabled = true;
            }
            else
            {
                txtTaiKhoan.Enabled = false;
                txtMatKhau.Enabled = false;
            }
        }

        public string getConnectionString()
        {
            string ConnectionString = "";
            if (radiobtnTaiKhoanWindow.Checked)
            {
                ConnectionString = string.Format(
                        "Data Source={0};Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=master",cbbMayChu.Text);
            }
            else
            {
                ConnectionString = string.Format("Data Source={0};Initial Catalog=master;uid={1}; Password={2}", cbbMayChu.Text, txtTaiKhoan.Text, txtMatKhau.Text);
            }
            return ConnectionString;

        }
        public string getConnectionString(string DataBaseName)
        {
            string ConnectionString = "";
            if (radiobtnTaiKhoanWindow.Checked)
            {
                ConnectionString = string.Format(
                        "Data Source={0};Initial Catalog={1};Persist Security Info=False;Integrated Security=SSPI", cbbMayChu.Text, DataBaseName);
            }
            else
            {
                ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2}; Password={3}", cbbMayChu.Text, DataBaseName, txtTaiKhoan.Text, txtMatKhau.Text);
            }
            return ConnectionString;

        }

        public void UpdateAttribute(string connString)
        {
            // Create config file to create 
            string fileName = string.Format(
                @"{0}{1}{2}", Application.StartupPath, "\\", "SMS.exe.config");

            // Updating connection string in file 
            //MessageBox.Show(String.Format("Updating connection string in file: {0}", fileName));

            XmlDocument xmlDom = new XmlDocument();
            xmlDom.Load(fileName);

            // Get XML node 
            XmlNode xmlNode = xmlDom.SelectSingleNode(
                "configuration/appSettings/add[@key='SQLConnection']");
            xmlNode.Attributes["value"].Value = connString;

            // Updating connection string in file 
            //MessageBox.Show(String.Format("Followind node of config file will be updated: {0}", xmlNode.InnerXml));

            // Save to disk
            xmlDom.Save(fileName);           
        }

        private bool CheckText()
        {
            if (cbbMayChu.Text.Equals(String.Empty))
            {
                errorProvider1.SetError(cbbMayChu, "Bạn chưa chọn máy chủ.");
                return false;
            }
            else
            {
                errorProvider1.Clear();
            }

            if (radiobtnTaiKhoanSqlServer.Checked)
            {
                if (txtTaiKhoan.Text.Equals(String.Empty))
                {
                    errorProvider1.SetError(txtTaiKhoan, "Hãy nhập tài khoản đăng nhập máy chủ.");
                    return false;
                }
                else
                {
                    errorProvider1.Clear();
                }

                if (txtMatKhau.Text.Equals(String.Empty))
                {
                    errorProvider1.SetError(txtMatKhau, "Hãy nhập mật khẩu đăng nhập máy chủ.");
                    return false;
                }
                else
                {
                    errorProvider1.Clear();
                }
            }
            else
            {
                errorProvider1.Clear();
            }

            if (cbbListDataBaseName.Text.Equals(String.Empty))
            {
                errorProvider1.SetError(cbbListDataBaseName, "Bạn chưa chọn cơ sở dữ liệu.");
                return false;
            }
            else
            {
                errorProvider1.Clear();
            }
            return true;
        }

        public bool checkConnection(string strConnection, ref string error)
        {
            bool flag = true;
            using (SqlConnection Conn = new SqlConnection(strConnection))
            {
                try
                {
                    Conn.Open();
                        
                }
                catch (SqlException ex)
                {
                    error = ex.Message;
                    flag = false;
                }
                finally
                {
                    Conn.Close();
                }

                return flag;
            }
        }

        private void btnTestConnect_Click(object sender, EventArgs e)
        {
            if (CheckText())
            {
                Enabled = false;
                StatusForm statusform = new StatusForm();
                statusform.MdiParent = this.MdiParent;
                statusform.Show("Vui lòng chờ kiểm tra kết nối đến cơ sở dữ liệu.");
                
                string error = "";

                if (checkConnection(getConnectionString(cbbListDataBaseName.Text), ref error))
                {
                    statusform.Close();
                    Enabled = true;
                    Cursor.Current = Cursors.Default;
                    MessageBox.Show("Kết nối đến data base thành công.");
                }
                else
                {
                    statusform.Close();
                    Enabled = true;
                    Cursor.Current = Cursors.Default;
                    MessageBox.Show(error);
                }
                           
            }             
        }

        private void btnSaveConnect_Click(object sender, EventArgs e)
        {
            if (CheckText())
            {                
                try
                {                    
                    UpdateAttribute(getConnectionString(cbbListDataBaseName.Text));                    
                    MessageBox.Show("Lưu cấu hình kết nối đến cơ sở dữ liệu thành công.");
                }
                catch (Exception ex)
                {                    
                    MessageBox.Show(ex.Message);
                    return;
                }
            }
        }

      
    }
}
